package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.entity.Attachs;
import cn.gson.crm.model.entity.Employee;
import cn.gson.crm.model.entity.Student;
import cn.gson.crm.model.po.FollowStudentPo;

public class StudentPoDao {

	// 一页显示的数据条数
	private Integer pageSize = 3;

	/**
	 * 保存附件信息
	 * 
	 * @param attach
	 * @return
	 * @throws SQLException
	 */
	public Integer saveAttach(Attachs attach) throws SQLException {
		String InsertSql = "insert into attach(model, file_name, file_path, file_size) values(?,?,?,?)";
		if (Jdbc.getInstace().executeUpdate(InsertSql, attach.getModel(), attach.getFileName(), attach.getFilePath(),
				attach.getFileSize())) {
			String sql = "select id from attach order by id desc limit 1";
			Map<String, Object> map = Jdbc.getInstace().executeQuery(sql);
			if (map != null) {
				return Integer.parseInt(map.get("id").toString());
			}
		}
		return null;
	}

	/**
	 * 获取学生状态为status，等级<gradeId的最新的nums条跟进记录
	 * 
	 * @param status
	 * @param gradeId
	 * @param nums
	 * @return
	 * @throws SQLException
	 */
	public List<FollowStudentPo> getNewFollow(String status, int gradeId, int nums) throws SQLException {
		String sql = "select * from (select * from student where stu_status=? "
				+ "and grade_id<? )form  JOIN  follow_up on "
				+ "form.id=follow_up.stu_id order by record_time desc limit ? ;";
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, status, gradeId, nums);
		List<FollowStudentPo> studentList = null;
		return buildStudentPoList(rows, studentList);

	}

	/**
	 * 获取状态为status,gradeId<5的学生 查询名字分页
	 * 
	 * @param status
	 * @param page
	 * @param stuName
	 * @return
	 * @throws SQLException
	 */
	public List<FollowStudentPo> getNormalStudentList(String status, String stuName, String isAllocation)
			throws SQLException {
		String sql = "select * from (select * from student where stu_status = ? and is_allocation=?";
		List<Object> params = new ArrayList<>();
		params.add(status);
		params.add(isAllocation);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ?";
			params.add("%" + stuName.trim() + "%");
		}
		sql += ") as form where form.grade_id<5 order by form.grade_id desc,form.stu_createtime desc";
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<FollowStudentPo> studentList = null;
		return buildStudentPoList(rows, studentList);
	}

	/**
	 * 关联附件
	 * 
	 * @param recordId
	 * @param atts
	 * @throws SQLException
	 */
	public void connectAttach(int recordId, String atts[]) throws SQLException {
		String sql = "update attach set record = ? where id = ?";
		for (String aid : atts) {
			Jdbc.getInstace().executeUpdate(sql, recordId, aid);
		}
	}

	/**
	 * 显示一次所有上传附件
	 * 
	 * @param record
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public List<Attachs> showAllAttach(int record, String model) throws SQLException {
		String sql = "select * from attach where record = ? and model = ? ";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, record, model);
		List<Attachs> attach = null;
		if (data != null) {
			attach = new ArrayList<>();
			for (Map<String, Object> map : data) {
				Attachs att = new Attachs();
				if (map.get("id") != null) {
					att.setId(Integer.parseInt(map.get("id").toString()));
				}
				if (map.get("file_name") != null) {
					att.setFileName((String) map.get("file_name"));
				}
				if (map.get("file_path") != null) {
					att.setFilePath((String) map.get("file_path"));
				}
				if (map.get("model") != null) {
					att.setModel((String) map.get("model"));
				}
				att.setRecord(Long.parseLong(map.get("record").toString()));
				att.setUploadTime((Date) map.get("upload_time"));
				att.setFileSize(Long.parseLong(map.get("file_size").toString()));
				attach.add(att);
			}
		}
		return attach;
	}

	/**
	 * 
	 * 获取咨询师所有潜在学员和通过名字查询
	 * 
	 * @param empId
	 * @param stuStatus
	 * @return
	 * @throws SQLException
	 */
	public List<FollowStudentPo> getFollowStudentList(Integer empId, String stuStatus, String isAllocation,
			String stuName) throws SQLException {
		String sql = "SELECT * FROM (SELECT * FROM student WHERE " + "counselor_id = ? AND stu_status = ?"
				+ " AND is_allocation = ? ";
		List<Object> params = new ArrayList<>();
		params.add(empId);
		params.add(stuStatus);
		params.add(isAllocation);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ? ";
			params.add("%" + stuName.trim() + "%");
		}
		sql += " ) AS form WHERE form.grade_id<5 ORDER by form.stu_createtime desc";
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<FollowStudentPo> followStudentList = null;
		return buildStudentPoList(rows, followStudentList);
	}

	/**
	 * 
	 * 获取所有已分配且状态正常学员列表
	 * 
	 * @param empId
	 * @return
	 * @throws SQLException
	 */

	public List<FollowStudentPo> getAllocatedStudentList(Integer empId, String stuStatus, String isAllocation,
			String stuName) throws SQLException {
		String sql = "SELECT * FROM (SELECT * FROM student WHERE " + "counselor_id = ? AND stu_status = ?"
				+ " AND is_allocation = ? ";
		List<Object> params = new ArrayList<>();
		params.add(empId);
		params.add(stuStatus);
		params.add(isAllocation);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ?  ";
			params.add("%" + stuName.trim() + "%");
		}
		sql += " ) AS form WHERE form.grade_id >4 ORDER by form.stu_createtime desc ";
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<FollowStudentPo> allocatedStudentList = null;
		return buildStudentPoList(rows, allocatedStudentList);
	}

	/**
	 * 分页 根据学生id查询跟进记录
	 * 
	 * @param stuId
	 * @return
	 * @throws SQLException
	 */
	public List<FollowStudentPo> getFollowRecordById(Integer page, String stuId, Integer empId) throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select m.*,(select count(1) from attach a where a.record = m.record_id and a.model='follow_up') attach from follow_up m "
				+ "where stu_id = ? and emp_id= ? order by record_time DESC LIMIT ?,? ";
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, stuId, empId, start, pageSize);
		List<FollowStudentPo> studentList = null;
		return buildStudentPoList(rows, studentList);
	}

	/**
	 * 获取咨询潜在和协助的学生跟进记录
	 * 
	 * @param stuId
	 * @param empId
	 * @return
	 * @throws SQLException
	 */
	public List<FollowStudentPo> getFollowRecordById(Integer stuId, Integer empId) throws SQLException {
		String sql = "select * from follow_up where stu_id = ? and emp_id=? order by record_time DESC";
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, stuId, empId);
		List<FollowStudentPo> studentList = null;
		return buildStudentPoList(rows, studentList);
	}

	/**
	 * 获取记录总条数
	 * 
	 * @param stuId
	 * @param empId
	 * @return
	 * @throws SQLException
	 */
	public Integer getRecordTotalPage(String stuId, Integer empId) throws SQLException {
		String sql = "select count(*)total from follow_up where stu_id = ? and emp_id=? ";
		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, stuId, empId);
		Integer count = 0;
		if (row != null) {
			count = Integer.parseInt(row.get("total").toString());
		}
		return (int) Math.ceil(count.doubleValue() / pageSize);

	}

	/**
	 * 通过学生id获取协助记录
	 * 
	 * @param stuId
	 * @return
	 * @throws SQLException
	 */
	public List<FollowStudentPo> getAssistRecord(Integer page, String stuId) throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "SELECT * FROM (SELECT * FROM allocation" + " WHERE stu_id=? and is_assist=1)as newform "
				+ "JOIN follow_up on newform.assist_from = follow_up.emp_id "
				+ "and newform.stu_id = follow_up.stu_id order by follow_up.record_time desc LIMIT ?,?;";
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, stuId, start, pageSize);
		List<FollowStudentPo> studentList = null;
		return buildStudentPoList(rows, studentList);
	}

	public boolean changeFollowStudentRecord(Integer empId, String stuId, String recordId, String content)
			throws SQLException {
		String sql = "UPDATE follow_up set content=? where record_id = ? and emp_id=? and stu_id = ?;";
		return Jdbc.getInstace().executeUpdate(sql, content, recordId, empId, stuId);
	}

	/**
	 * 获取咨询师所协助的学生列表
	 * 
	 * @param empId
	 * @return
	 * @throws SQLException
	 */
	public List<FollowStudentPo> getMyAssitStudentList(Integer empId, int is_assist, String isAllocated, String stuName)
			throws SQLException {
		String sql = "SELECT * FROM (SELECT * " + "FROM(select * from allocation WHERE  "
				+ "assist_from=? and is_assist=? ";
		List<Object> params = new ArrayList<>();
		params.add(empId);
		params.add(is_assist);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and student.stu_name like ? ";
			params.add("%" + stuName.trim() + "%");
		}
		sql += ") as form JOIN student on form.stu_id = student.id  and student.is_allocation = ?)as totalform WHERE totalform.grade_id =1 or totalform.grade_id =2 or totalform.grade_id =3 or totalform.grade_id =4";
		params.add(isAllocated);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<FollowStudentPo> assitStudentList = null;
		if (rows != null) {
			assitStudentList = new ArrayList<>();
			for (Map<String, Object> map : rows) {
				FollowStudentPo po = getStudentInfo(map.get("stu_id").toString());
				po.setAsCreateTime((Date) map.get("ascreate_date"));
				assitStudentList.add(po);
			}
		}
		return assitStudentList;
	}

	/**
	 * 获取咨询师所协助的学生总页数
	 * 
	 * @param empId
	 * @param page
	 * @return
	 * @throws SQLException
	 */
	public Integer getAssitStuTotalPage(Integer empId, int is_assist, String isAllocated, String stuName)
			throws SQLException {
		String sql = "SELECT count(*)total FROM (SELECT * " + "FROM(select * from allocation WHERE  "
				+ "assist_from=? and is_assist=?  ";
		List<Object> params = new ArrayList<>();
		params.add(empId);
		params.add(is_assist);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and student.stu_name like ?  ";
			params.add("%" + stuName.trim() + "%");
		}
		sql += ") as form JOIN student on form.stu_id = student.id  and student.is_allocation = ?)as totalform WHERE totalform.grade_id =1 or totalform.grade_id =2 or totalform.grade_id =3 or totalform.grade_id =4";
		params.add(isAllocated);
		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer count = 0;
		if (row != null) {
			count = Integer.parseInt(row.get("total").toString());
		}
		System.out.println(count);
		return (int) Math.ceil(count.doubleValue() / pageSize);

	}

	/**
	 * 获取协助记录总页数
	 * 
	 * @param stuId
	 * @return
	 * @throws SQLException
	 */
	public Integer getAssistTotalPage(String stuId) throws SQLException {
		String sql = "SELECT count(*)total FROM (" + "SELECT * FROM allocation  " + "WHERE stu_id=? and is_assist=1)as "
				+ "newform JOIN follow_up on " + "newform.assist_from = follow_up.emp_id "
				+ "and newform.stu_id = follow_up.stu_id";
		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, stuId);
		Integer count = 0;
		if (row != null) {
			count = Integer.parseInt(row.get("total").toString());
		}
		return (int) Math.ceil(count.doubleValue() / pageSize);
	}

	/**
	 * 根据学生id查询学生信息
	 * 
	 * @param stuId
	 * @return
	 * @throws SQLException
	 */
	public FollowStudentPo getStudentInfo(String stuId) throws SQLException {
		String sql = "select * from student  join allocation  where id = ?";
		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, stuId);
		return buildStudentPo(row);

	}

	/**
	 * 取到集合之后再进行分页
	 * 
	 * @param request
	 * @param list
	 * @param pageSize
	 * @param page
	 * @return
	 */
	public List<FollowStudentPo> getListThenDividPage(HttpServletRequest request, List<FollowStudentPo> list,
			int page) {
		List<FollowStudentPo> subList = null;
		Integer totalCount = list.size();
		/* 计算出总共能分成多少页 */
		int pageCount = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
		if (totalCount > 0) {
			if (page <= pageCount) {
				// 当前页数为第一页
				if (page == 1) {
					// 数据总数小于每页显示的数据条数
					if (totalCount <= pageSize) {
						// 截止到总的数据条数(当前数据不足一页，按一页显示)，这样才不会出现数组越界异常
						subList = list.subList(0, totalCount);
					} else {
						subList = list.subList(0, pageSize);
					}
				} else {
					// 截取起始下标
					int fromIndex = (page - 1) * pageSize;
					// 截取截止下标
					int toIndex = page * pageSize;
					/* 计算截取截止下标 */
					if ((totalCount - toIndex) % pageSize >= 0) {
						toIndex = page * pageSize;
					} else {
						toIndex = (page - 1) * pageSize + (totalCount % pageSize);
					}
					if (totalCount >= toIndex) {
						subList = list.subList(fromIndex, toIndex);
					}
				}
			} else {
				subList = null;
			}
		}
		request.setAttribute("totalPage", pageCount);
		return subList;

	}

	/**
	 * 
	 * 获取不同等级的潜在客户集合
	 * 
	 * @throws SQLException
	 */
	public List<FollowStudentPo> getFilterStudentList(Integer empId, String stuStatus, String isAllocation,
			String gradeId) throws SQLException {
		String sql = "SELECT * from  student where counselor_id = ? and stu_status=? AND is_allocation = ? ";
		List<Object> params = new ArrayList<>();
		params.add(empId);
		params.add(stuStatus);
		params.add(isAllocation);
		sql = buildParamAndSql(params, sql, gradeId);
		return queryStudent(sql, params.toArray());
	}

	/**
	 * 通过客户id删除
	 * 
	 * @param stuId
	 * @return
	 * @throws SQLException
	 */
	public Boolean deleStudentById(Integer stuId) throws SQLException {
		String sql = "update student set stu_status ='删除' where id = ?";
		return Jdbc.getInstace().executeUpdate(sql, stuId);
	}

	/**
	 * 跟进一条记录
	 * 
	 * @param stuId
	 * @param content
	 * @return
	 * @throws SQLException
	 */
	/*
	 * public Boolean insertOneRecord(FollowStudentPo po,String empId) throws
	 * SQLException{ String sql =
	 * "INSERT INTO `crm`.`follow_up` (`emp_id`, `stu_id`, `content`, " +
	 * "`next_time`, `flow_time`) VALUES ( ?, ?, ?, ?,?);"; return
	 * Jdbc.getInstace().executeUpdate(sql, empId,po.getId(),po.getContent(),
	 * po.getNextTime(),po.getFollowTime()); }
	 */
	/**
	 * 保存跟进信息
	 * 
	 * @param po
	 * @param empId
	 * @return
	 * @throws SQLException
	 */
	public Integer insertOneRecord(FollowStudentPo po, String empId) throws SQLException {
		String insertSql = "INSERT INTO `crm`.`follow_up` (`emp_id`, `stu_id`, `content`, "
				+ "`next_time`, `flow_time`) VALUES ( ?, ?, ?, ?,?);";
		if (Jdbc.getInstace().executeUpdate(insertSql, empId, po.getId(), po.getContent(), po.getNextTime(),
				po.getFollowTime())) {
			String sql = "select record_id from follow_up order by record_id desc limit 1";
			Map<String, Object> map = Jdbc.getInstace().executeQuery(sql);
			if (map != null) {
				return Integer.parseInt(map.get("record_id").toString());
			}
		}
		return null;
	}

	/**
	 * 查找学员
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private List<FollowStudentPo> queryStudent(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params);
		List<FollowStudentPo> studentList = null;
		return buildStudentPoList(rows, studentList);
	}

	/**
	 * 组装参数
	 * 
	 * @param params
	 *            WHERE form.stu_name LIKE '%周%';
	 * @param sql
	 * @param gradeId
	 * @return
	 */
	private String buildParamAndSql(List<Object> params, String sql, String gradeId) {
		if (!StringUtils.isNullOrEmpty(gradeId)) {
			sql += " and grade_id =? ";
			params.add(gradeId);
		}
		return sql;
	}

	/**
	 * 组装学员信息列表
	 * 
	 * @param rows
	 * @return
	 * @throws SQLException
	 * @throws NumberFormatException
	 */
	private List<FollowStudentPo> buildStudentPoList(List<Map<String, Object>> rows, List<FollowStudentPo> studentList)
			throws NumberFormatException, SQLException {
		studentList = new ArrayList<>();
		if (rows != null) {
			for (Map<String, Object> row : rows) {
				studentList.add(buildStudentPo(row));
			}
		}
		return studentList;
	}

	/**
	 * 组装学员信息
	 * 
	 * @param row
	 * @return
	 * @throws SQLException
	 * @throws NumberFormatException
	 */
	public FollowStudentPo buildStudentPo(Map<String, Object> row) throws NumberFormatException, SQLException {
		FollowStudentPo po = null;
		if (row != null) {
			po = new FollowStudentPo();
			if (row.get("id") != null) {
				po.setId(Integer.parseInt(row.get("id").toString()));
			}
			if (row.get("record_id") != null) {
				po.setRecordId(Integer.parseInt(row.get("record_id").toString()));
			}
			po.setAsCreateTime((Date) row.get("ascreate_date"));
			if (row.get("assist_from") != null) {
				po.setAssitstFrom(Integer.parseInt(row.get("assist_from").toString()));
				po.setAssitstEmpName(new EmploDao()
						.emploGetById("正常", Integer.parseInt(row.get("assist_from").toString())).getUserName());

			}
			if (row.get("attach") != null) {
				po.setAttach(Integer.parseInt(row.get("attach").toString()));
			}

			po.setIsAllocation((String) row.get("is_allocation"));
			po.setName((String) row.get("stu_name"));
			po.setGender((String) row.get("stu_gender"));
			po.setSource((String) row.get("stu_source"));
			po.setEducation((String) row.get("stu_education"));
			po.setPhone((String) row.get("stu_phone"));
			po.setAddress((String) row.get("stu_address"));
			po.setIdCard((String) row.get("stu_idcard"));
			po.setQq((String) row.get("stu_qq"));
			po.setFatherName((String) row.get("stu_fathername"));
			po.setFatherTel((String) row.get("stu_fathertel"));
			po.setMotherName((String) row.get("stu_mothername"));
			po.setMotherTel((String) row.get("stu_mothertel"));
			po.setCreator((String) row.get("stu_creator"));
			if (row.get("grade_id") != null) {
				po.setGradeId(Integer.parseInt(row.get("grade_id").toString()));
			}
			po.setStatus((String) row.get("stu_status"));
			po.setCreateTime((Date) row.get("stu_createtime"));
			if (row.get("record_id") != null) {
				po.setRecordId(Integer.parseInt(row.get("record_id").toString()));
			}
			po.setContent((String) row.get("content"));
			po.setFollowTime((Date) row.get("flow_time"));
			po.setRecordTime((Date) row.get("record_time"));
			po.setNextTime((Date) row.get("next_time"));
			if (row.get("counselor_id") != null) {
				po.setCounselorId(Integer.parseInt(row.get("counselor_id").toString()));
				// 获取咨询师姓名
				Employee emp = new EmploDao().emploGetById("正常", Integer.parseInt(row.get("counselor_id").toString()));
				if (emp != null) {
					po.setCounselorName(emp.getUserName());
				}
			}
			if(row.get("class_type")!=null){
				po.setClassType(Integer.parseInt(row.get("class_type").toString()));
			}
		}
		return po;
	}

}
